'use strict';

const Service = require('egg').Service;
const moment = require('moment');

/**
 * 审批流服务类
 * 处理审批相关的数据库操作和业务逻辑
 */
class ApprovalService extends Service {
  /**
   * 获取审批统计信息
   * @param {Object} params - 查询参数
   * @param {number} params.person_id - 当前用户ID
   * @returns {Promise<Object>} 统计结果
   */
  async getApprovalStatistics(params) {
    const {
      person_id
    } = params;
    const results = {};

    // 1. 总审批实例数量
    const totalResult = await this.app.mysql.query(
      'SELECT COUNT(*) as count FROM ap_applications'
    );
    results.total_count = totalResult[0].count;

    // 2. 按状态统计
    const statusResult = await this.app.mysql.query(
      `SELECT 
      status, 
      COUNT(*) as count 
     FROM ap_applications 
     GROUP BY status`
    );

    // 初始化各状态数量为0
    results.approved_count = 0;
    results.pending_count = 0;
    results.rejected_count = 0;
    results.withdrawn_count = 0;

    // 填充各状态数量
    for (const item of statusResult) {
      switch (item.status) {
        case 'approved':
          results.approved_count = item.count;
          break;
        case 'pending':
        case 'processing':
          results.pending_count += item.count;
          break;
        case 'rejected':
          results.rejected_count = item.count;
          break;
        case 'withdrawn':
          results.withdrawn_count = item.count;
          break;
      }
    }

    // 3. 我已审批的数量
    const myProcessedResult = await this.app.mysql.query(
      `SELECT COUNT(DISTINCT application_id) as count 
     FROM ap_approval_records 
     WHERE approver_id = ?`,
      [person_id]
    );
    results.my_processed_count = myProcessedResult[0].count;

    // 4. 待我审批的数量
    const myPendingResult = await this.app.mysql.query(
      `SELECT COUNT(*) as count 
     FROM ap_approval_flows 
     WHERE 
       approver_id = ? 
       AND status = 'pending' 
       AND application_id IN (
         SELECT application_id 
         FROM ap_applications 
         WHERE status = 'pending' OR status = 'processing'
       )`,
      [person_id]
    );
    results.my_pending_count = myPendingResult[0].count;

    return results;
  }

  /**
   * 发起审批
   * @param {Object} data - 审批数据
   * @param {string} data.title - 审批标题
   * @param {string} data.content - 审批内容
   * @param {number} data.applicant_id - 申请人ID
   * @param {string} [data.work_members] - 工作成员，多个人员ID用逗号分隔
   * @param {string} data.estimated_start_date - 预计开始时间
   * @param {string} data.estimated_end_date - 预计结束时间
   * @param {Array} data.approvers - 审批人列表
   * @param {Array} data.output_types - 产出类型列表
   * @returns {Promise<Object>} 创建的审批信息
   */
  async create(data) {
    const {
      app
    } = this;
    const conn = await app.mysql.beginTransaction(); // 开启事务

    try {
      // 1. 创建审批申请记录
      const applicationResult = await conn.insert('ap_applications', {
        title: data.title,
        content: data.content,
        applicant_id: data.applicant_id,
        work_members: data.work_members,
        estimated_start_date: data.estimated_start_date,
        estimated_end_date: data.estimated_end_date,
        total_levels: data.approvers.length,
        current_level: 1,
        status: 'processing', // 直接进入审批中状态
        created_at: new Date(),
        updated_at: new Date()
      });

      const applicationId = applicationResult.insertId;

      // 2. 创建审批流程记录
      for (const approver of data.approvers) {
        await conn.insert('ap_approval_flows', {
          application_id: applicationId,
          level: approver.level,
          approver_id: approver.approver_id,
          status: 'pending',
          sort_order: approver.level
        });
      }

      // 3. 创建产出记录
      for (const type of data.output_types) {
        await conn.insert('ap_output_attachments', {
          application_id: applicationId,
          type: type,
          status: 'pending',
          created_at: new Date(),
          updated_at: new Date()
        });
      }

      await conn.commit(); // 提交事务

      // 4. 返回创建的审批详情
      return await this.findById(applicationId);

    } catch (error) {
      await conn.rollback(); // 回滚事务
      throw error;
    }
  }

  /**
   * 获取审批列表
   * @param {Object} params - 查询参数
   * @param {string} [params.status] - 审批状态
   * @param {number} [params.applicant_id] - 申请人ID
   * @param {number} [params.approver_id] - 审批人ID
   * @param {string} [params.start_date] - 开始日期
   * @param {string} [params.end_date] - 结束日期
   * @param {number} [params.page=1] - 页码
   * @param {number} [params.page_size=10] - 每页条数
   * @returns {Promise<Object>} 包含列表和分页信息的对象
   */
  async findAll(params) {
    const {
      status,
      applicant_id,
      approver_id,
      start_date,
      end_date,
      page = 1,
      page_size = 10
    } = params;

    // 构建 WHERE 条件
    let conditions = [];
    const queryParams = [];

    if (status) {
      conditions.push('a.status = ?');
      queryParams.push(status);
    }

    if (applicant_id) {
      conditions.push('a.applicant_id = ?');
      queryParams.push(applicant_id);
    }

    if (start_date) {
      conditions.push('a.created_at >= ?');
      queryParams.push(`${start_date} 00:00:00`);
    }

    if (end_date) {
      conditions.push('a.created_at <= ?');
      queryParams.push(`${end_date} 23:59:59`);
    }

    // 处理审批人筛选
    let flowJoin = '';
    let flowCondition = '';

    if (approver_id) {
      flowJoin = `INNER JOIN ap_approval_flows f ON a.application_id = f.application_id`;
      flowCondition = `AND f.approver_id = ?`;
      queryParams.push(approver_id);
    }

    // 计算总数
    const countSql = `
      SELECT COUNT(DISTINCT a.application_id) as total 
      FROM ap_applications a
      ${flowJoin}
      WHERE ${conditions.length ? conditions.join(' AND ') : '1=1'} ${flowCondition}
    `;

    const totalResult = await this.app.mysql.query(countSql, queryParams);
    const total = totalResult[0].total;

    // 查询数据
    const offset = (page - 1) * page_size;
    const sql = `
      SELECT 
        a.*,
        p.person_name as applicant_name,
        (SELECT GROUP_CONCAT(CONCAT(p2.person_id, ':', p2.person_name) SEPARATOR ',') 
         FROM ap_approval_flows f 
         LEFT JOIN tb_person p2 ON f.approver_id = p2.person_id 
         WHERE f.application_id = a.application_id 
         ORDER BY f.level) as approvers,
        (SELECT GROUP_CONCAT(type SEPARATOR ',') 
         FROM ap_output_attachments o 
         WHERE o.application_id = a.application_id) as output_types,
        (SELECT COUNT(*) 
         FROM ap_output_attachments o 
         WHERE o.application_id = a.application_id AND o.status = 'completed') as completed_output_count,
        (SELECT COUNT(*) 
         FROM ap_output_attachments o 
         WHERE o.application_id = a.application_id) as total_output_count
      FROM 
        ap_applications a
        LEFT JOIN tb_person p ON a.applicant_id = p.person_id
        ${flowJoin}
      WHERE 
        ${conditions.length ? conditions.join(' AND ') : '1=1'} ${flowCondition}
      GROUP BY 
        a.application_id
      ORDER BY 
        a.created_at DESC
      LIMIT ? OFFSET ?
    `;

    const result = await this.app.mysql.query(sql, [...queryParams, page_size, offset]);

    return {
      list: result,
      pagination: {
        total,
        page: parseInt(page),
        page_size: parseInt(page_size),
        total_pages: Math.ceil(total / page_size)
      }
    };
  }

  // /**
  //  * 根据ID获取审批详情
  //  * @param {number} id - 审批申请ID
  //  * @returns {Promise<Object|null>} 审批详情，不存在时返回null
  //  */
  // async findById(id) {
  //   // 查询审批基本信息
  //   const applicationResult = await this.app.mysql.query(
  //     'SELECT a.*, p.person_name as applicant_name FROM ap_applications a LEFT JOIN tb_person p ON a.applicant_id = p.person_id WHERE a.application_id = ?',
  //     [id]
  //   );

  //   if (!applicationResult.length) {
  //     return null;
  //   }

  //   const application = applicationResult[0];

  //   // 查询审批流程
  //   const flowsResult = await this.app.mysql.query(
  //     `SELECT f.*, p.person_name as approver_name 
  //      FROM ap_approval_flows f 
  //      LEFT JOIN tb_person p ON f.approver_id = p.person_id 
  //      WHERE f.application_id = ? 
  //      ORDER BY f.level`,
  //     [id]
  //   );

  //   // 查询审批记录
  //   const recordsResult = await this.app.mysql.query(
  //     `SELECT r.*, p.person_name as approver_name 
  //      FROM ap_approval_records r 
  //      LEFT JOIN tb_person p ON r.approver_id = p.person_id 
  //      WHERE r.application_id = ? 
  //      ORDER BY r.level`,
  //     [id]
  //   );

  //   // 查询产出记录
  //   const outputsResult = await this.app.mysql.query(
  //     `SELECT o.* 
  //      FROM ap_output_attachments o 
  //      WHERE o.application_id = ? 
  //      ORDER BY o.type`,
  //     [id]
  //   );

  //   const work_members_name = await this.app.mysql.query(
  //     `SELECT person_name,person_id
  //      FROM tb_person
  //      WHERE person_id IN (${application.work_members});`
  //   );

  //   // 构建完整的审批详情
  //   return {
  //     ...application,
  //     work_members_name,
  //     flows: flowsResult,
  //     records: recordsResult,
  //     outputs: outputsResult
  //   };
  // }

  /**
   * 根据ID获取审批详情
   * @param {number} id - 审批申请ID
   * @returns {Promise<Object|null>} 审批详情，不存在时返回null
   */
  async findById(id) {
    // 查询审批基本信息
    const applicationResult = await this.app.mysql.query(
      'SELECT a.*, p.person_name as applicant_name FROM ap_applications a LEFT JOIN tb_person p ON a.applicant_id = p.person_id WHERE a.application_id = ?',
      [id]
    );

    if (!applicationResult.length) {
      return null;
    }

    const application = applicationResult[0];

    // 查询审批流程和审批记录的关联数据
    const flowsRecordsResult = await this.app.mysql.query(
      `SELECT 
      f.flow_id,
      f.application_id,
      f.level,
      f.approver_id,
      f.status as flow_status,
      f.sort_order,
      p.person_name as approver_name,
      r.record_id,
      r.result,
      r.comment,
      r.approved_at as record_created_at
     FROM ap_approval_flows f
     LEFT JOIN tb_person p ON f.approver_id = p.person_id
     LEFT JOIN ap_approval_records r ON f.flow_id = r.flow_id
     WHERE f.application_id = ?
     ORDER BY f.sort_order`,
      [id]
    );

    // 构建flows_records数组，将流程和记录关联
    const flows_records = [];
    const flowsMap = new Map();

    // 先处理所有流程记录
    for (const record of flowsRecordsResult) {
      if (!flowsMap.has(record.flow_id)) {
        flowsMap.set(record.flow_id, {
          flow_id: record.flow_id,
          application_id: record.application_id,
          level: record.level,
          approver_id: record.approver_id,
          approver_name: record.approver_name,
          status: record.flow_status,
          sort_order: record.sort_order,
          records: [] // 修改为数组，默认为空数组
        });
      }

      // 如果有审批记录，添加到对应的流程中
      if (record.record_id) {
        const flow = flowsMap.get(record.flow_id);
        flow.records.push({
          record_id: record.record_id,
          result: record.result,
          comment: record.comment,
          approved_at: record.record_created_at,
          approver_name: record.approver_name
        });
      }
    }

    // 转换为数组
    for (const flow of flowsMap.values()) {
      flows_records.push(flow);
    }

    // 查询产出记录 - 修改后，关联tb_person表获取上传人名称
    const outputsResult = await this.app.mysql.query(
      `SELECT 
        o.*,
        p.person_name as uploaded_by_name
      FROM 
        ap_output_attachments o
      LEFT JOIN 
        tb_person p ON o.uploaded_by = p.person_id
      WHERE 
        o.application_id = ? 
      ORDER BY 
        o.type`,
      [id]
    );

    // 查询工作成员名称
    const workMembersName = application.work_members ?
      await this.app.mysql.query(
        `SELECT person_name, person_id
         FROM tb_person
         WHERE person_id IN (${application.work_members});`
      ) : [];

    // 构建完整的审批详情
    return {
      ...application,
      work_members_name: workMembersName,
      flows: flows_records.map(flow => ({
        flow_id: flow.flow_id,
        application_id: flow.application_id,
        level: flow.level,
        approver_id: flow.approver_id,
        approver_name: flow.approver_name,
        status: flow.status,
        sort_order: flow.sort_order
      })),
      records: flows_records
        .flatMap(flow => flow.records.map(record => ({
          record_id: record.record_id,
          flow_id: flow.flow_id,
          application_id: flow.application_id,
          level: flow.level,
          approver_id: flow.approver_id,
          approver_name: flow.approver_name,
          result: record.result,
          comment: record.comment,
          approved_at: record.approved_at
        }))),
      outputs: outputsResult,
      // 新增的flows_records，包含流程和记录的关联数据
      flows_records: flows_records
    }
  }

  /**
   * 处理审批
   * @param {Object} data - 审批数据
   * @param {number} data.application_id - 审批申请ID
   * @param {number} data.flow_id - 审批流程ID
   * @param {number} data.approver_id - 审批人ID
   * @param {string} data.result - 审批结果
   * @param {string} [data.comment] - 审批意见
   * @returns {Promise<Object>} 处理结果
   */
  async handleApproval(data) {
    const {
      app
    } = this;
    const conn = await app.mysql.beginTransaction(); // 开启事务

    try {
      // 1. 验证权限和状态
      const flowResult = await conn.query(
        'SELECT * FROM ap_approval_flows WHERE flow_id = ? AND application_id = ?',
        [data.flow_id, data.application_id]
      );

      if (!flowResult.length) {
        await conn.rollback();
        return {
          error: 'flow_not_found',
          message: '审批流程不存在'
        };
      }

      const flow = flowResult[0];

      // 检查是否有权限审批
      if (flow.approver_id !== data.approver_id) {
        await conn.rollback();
        return {
          error: 'no_permission',
          message: '您无权处理该审批'
        };
      }

      // 检查审批状态
      if (flow.status !== 'pending') {
        await conn.rollback();
        return {
          error: 'already_processed',
          message: '该审批已处理'
        };
      }

      // 2. 获取审批申请信息
      const applicationResult = await conn.query(
        'SELECT * FROM ap_applications WHERE application_id = ?',
        [data.application_id]
      );

      if (!applicationResult.length) {
        await conn.rollback();
        return {
          error: 'application_not_found',
          message: '审批申请不存在'
        };
      }

      const application = applicationResult[0];

      // 检查是否当前审批级别
      if (application.current_level !== flow.level) {
        await conn.rollback();
        return {
          error: 'not_current_level',
          message: '当前审批尚未流转到该级别'
        };
      }

      // 3. 更新审批流程状态
      await conn.update('ap_approval_flows', {
        status: data.result,
      }, {
        where: {
          flow_id: data.flow_id
        }
      });

      // 4. 创建审批记录
      await conn.insert('ap_approval_records', {
        flow_id: data.flow_id,
        application_id: data.application_id,
        approver_id: data.approver_id,
        level: flow.level,
        result: data.result,
        comment: data.comment,
        approved_at: new Date()
      });

      // 5. 根据审批结果更新审批申请状态
      let isFinalApproval = false;
      if (data.result === 'rejected') {
        // 拒绝：直接结束流程
        await conn.update('ap_applications', {
          status: 'rejected',
          completed_at: new Date(),
          updated_at: new Date()
        }, {
          where: {
            application_id: data.application_id
          }
        });
      } else {
        // 同意：判断是否是最后一级
        if (flow.level === application.total_levels) {
          // 最后一级：流程结束
          await conn.update('ap_applications', {
            status: 'approved',
            completed_at: new Date(),
            updated_at: new Date()
          }, {
            where: {
              application_id: data.application_id
            }
          });
          isFinalApproval = true;
        } else {
          // 不是最后一级：进入下一级
          await conn.update('ap_applications', {
            current_level: flow.level + 1,
            updated_at: new Date()
          }, {
            where: {
              application_id: data.application_id
            }
          });
        }
      }

      // 6. 如果是最后一级同意，执行额外的业务处理
      if (isFinalApproval) {
        // 获取审批详情
        const approvalDetail = await this.findById(data.application_id);

        // 获取工作成员列表
        const workMembers = approvalDetail.work_members ?
          approvalDetail.work_members.split(',').map(id => parseInt(id.trim())) : [];

        // 获取日期范围和标题
        const startDate = approvalDetail.estimated_start_date;
        const endDate = approvalDetail.estimated_end_date;
        const destination = approvalDetail.title;

        // 验证必要信息
        if (!startDate || !endDate || !destination) {
          this.ctx.logger.warn(`审批ID: ${data.application_id} 缺少必要信息，无法执行去向处理`);
        } else if (workMembers.length === 0) {
          this.ctx.logger.warn(`审批ID: ${data.application_id} 没有工作成员，无法执行去向处理`);
        } else {
          // 调用destination service的handlePersonDestination方法
          const destinationService = this.ctx.service.destination;
          if (destinationService && destinationService.handlePersonDestination) {
            // 生成日期范围
            const dateRange = this.generateDateRange(startDate, endDate);

            // 分次调用handlePersonDestination方法
            for (const personId of workMembers) {
              for (const specificDate of dateRange) {
                try {
                  await destinationService.handlePersonDestination({
                    person_id: personId,
                    specific_date: specificDate,
                    destination: destination
                  });
                  this.ctx.logger.info(`成功处理人员 ${personId} 在 ${specificDate} 的去向: ${destination}`);
                } catch (error) {
                  this.ctx.logger.error(`处理人员 ${personId} 在 ${specificDate} 的去向失败:`, error);
                  // 如果需要严格的事务一致性，可以在这里抛出错误使整个事务回滚
                  // throw new Error(`处理人员 ${personId} 的去向失败: ${error.message}`);
                }
              }
            }
          } else {
            this.ctx.logger.error('未找到destination服务或handlePersonDestination方法');
          }
        }
      }

      await conn.commit(); // 提交事务

      // 7. 返回更新后的审批详情
      return await this.findById(data.application_id);
    } catch (error) {
      await conn.rollback(); // 回滚事务
      throw error;
    }
  }

  /**
   * 生成日期范围数组
   * @param {string|Date} startDate - 开始日期
   * @param {string|Date} endDate - 结束日期
   * @returns {string[]} 日期数组，格式为YYYY-MM-DD
   */
  generateDateRange(startDate, endDate) {
    const dates = [];
    const start = moment(startDate);
    const end = moment(endDate);

    if (!start.isValid() || !end.isValid()) {
      return dates;
    }

    const currentDate = start.clone();

    while (currentDate.isSameOrBefore(end)) {
      dates.push(currentDate.format('YYYY-MM-DD'));
      currentDate.add(1, 'day');
    }

    return dates;
  }

  /**
   * 撤回审批
   * @param {number} applicationId - 审批申请ID
   * @param {number} applicantId - 申请人ID
   * @returns {Promise<Object>} 处理结果
   */
  async withdrawApproval(applicationId, applicantId) {
    const {
      app
    } = this;
    const conn = await app.mysql.beginTransaction(); // 开启事务

    try {
      // 1. 验证权限和状态
      const applicationResult = await conn.query(
        'SELECT * FROM ap_applications WHERE application_id = ?',
        [applicationId]
      );

      if (!applicationResult.length) {
        await conn.rollback();
        return {
          error: 'application_not_found',
          message: '审批申请不存在'
        };
      }

      const application = applicationResult[0];

      // 检查是否有权限撤回
      if (application.applicant_id !== applicantId) {
        await conn.rollback();
        return {
          error: 'no_permission',
          message: '您无权撤回该审批'
        };
      }

      // 检查是否可以撤回（只有待提交或审批中的可以撤回）
      if (application.status !== 'pending' && application.status !== 'processing') {
        await conn.rollback();
        return {
          error: 'cannot_withdraw',
          message: '该审批已处理，无法撤回'
        };
      }

      // 2. 更新审批申请状态
      await conn.update('ap_applications', {
        status: 'withdrawn',
        completed_at: new Date(),
        updated_at: new Date()
      }, {
        where: {
          application_id: applicationId
        }
      });

      await conn.commit(); // 提交事务

      // 3. 返回更新后的审批详情
      return await this.findById(applicationId);

    } catch (error) {
      await conn.rollback(); // 回滚事务
      throw error;
    }
  }

  /**
   * 获取用户待审批列表
   * @param {Object} params - 查询参数
   * @param {number} params.approver_id - 审批人ID
   * @param {number} [params.page=1] - 页码
   * @param {number} [params.page_size=10] - 每页条数
   * @returns {Promise<Object>} 包含列表和分页信息的对象
   */
  async findPendingByApprover(params) {
    const {
      approver_id,
      page = 1,
      page_size = 10
    } = params;

    // 计算总数
    const countSql = `
      SELECT COUNT(*) as total 
      FROM ap_applications a
      INNER JOIN ap_approval_flows f ON a.application_id = f.application_id
      WHERE 
        f.approver_id = ? 
        AND f.status = 'pending' 
        AND a.current_level = f.level 
        AND a.status = 'processing'
    `;

    const totalResult = await this.app.mysql.query(countSql, [approver_id]);
    const total = totalResult[0].total;

    // 查询数据
    const offset = (page - 1) * page_size;
    const sql = `
      SELECT 
        a.*,
        p.person_name as applicant_name,
        f.level as current_approval_level,
        f.flow_id
      FROM 
        ap_applications a
        INNER JOIN ap_approval_flows f ON a.application_id = f.application_id
        LEFT JOIN tb_person p ON a.applicant_id = p.person_id
      WHERE 
        f.approver_id = ? 
        AND f.status = 'pending' 
        AND a.current_level = f.level 
        AND a.status = 'processing'
      ORDER BY 
        a.created_at DESC
      LIMIT ? OFFSET ?
    `;

    const result = await this.app.mysql.query(sql, [approver_id, page_size, offset]);

    return {
      list: result,
      pagination: {
        total,
        page: parseInt(page),
        page_size: parseInt(page_size),
        total_pages: Math.ceil(total / page_size)
      }
    };
  }

  /**
   * 获取用户已审批列表
   * @param {Object} params - 查询参数
   * @param {number} params.approver_id - 审批人ID
   * @param {number} [params.page=1] - 页码
   * @param {number} [params.page_size=10] - 每页条数
   * @returns {Promise<Object>} 包含列表和分页信息的对象
   */
  async findApprovedByApprover(params) {
    const {
      approver_id,
      page = 1,
      page_size = 10
    } = params;

    // 计算总数
    const countSql = `
    SELECT COUNT(DISTINCT a.application_id) as total 
    FROM ap_applications a
    INNER JOIN ap_approval_records r ON a.application_id = r.application_id
    WHERE 
      r.approver_id = ?
  `;

    const totalResult = await this.app.mysql.query(countSql, [approver_id]);
    const total = totalResult[0].total;

    // 查询数据 - 修复GROUP BY问题
    const offset = (page - 1) * page_size;
    const sql = `
    SELECT 
      a.*,
      p.person_name as applicant_name,
      r.result as my_result,
      r.comment as my_comment,
      r.approved_at as my_approved_time
    FROM 
      ap_applications a
      INNER JOIN (
        SELECT 
          application_id,
          result,
          comment,
          approved_at
        FROM 
          ap_approval_records
        WHERE 
          approver_id = ?
        ORDER BY 
          approved_at DESC
      ) r ON a.application_id = r.application_id
      LEFT JOIN tb_person p ON a.applicant_id = p.person_id
    GROUP BY 
      a.application_id, r.result, r.comment, r.approved_at, 
      a.title, a.content, a.applicant_id, a.work_members, 
      a.estimated_start_date, a.estimated_end_date, a.total_levels, 
      a.current_level, a.status, a.created_at, a.updated_at, 
      a.completed_at, p.person_name
    ORDER BY 
      r.approved_at DESC
    LIMIT ? OFFSET ?
  `;

    const result = await this.app.mysql.query(sql, [approver_id, page_size, offset]);

    return {
      list: result,
      pagination: {
        total,
        page: parseInt(page),
        page_size: parseInt(page_size),
        total_pages: Math.ceil(total / page_size)
      }
    };
  }

  /**
   * 获取用户发起的审批列表
   * @param {Object} params - 查询参数
   * @param {number} params.applicant_id - 申请人ID
   * @param {string} [params.status] - 审批状态
   * @param {number} [params.page=1] - 页码
   * @param {number} [params.page_size=10] - 每页条数
   * @returns {Promise<Object>} 包含列表和分页信息的对象
   */
  async findMyApplications(params) {
    const {
      applicant_id,
      status,
      page = 1,
      page_size = 10
    } = params;

    // 构建 WHERE 条件
    let conditions = ['a.applicant_id = ?'];
    const queryParams = [applicant_id];

    if (status) {
      conditions.push('a.status = ?');
      queryParams.push(status);
    }

    // 计算总数
    const countSql = `
      SELECT COUNT(*) as total 
      FROM ap_applications a
      WHERE ${conditions.join(' AND ')}
    `;

    const totalResult = await this.app.mysql.query(countSql, queryParams);
    const total = totalResult[0].total;

    // 查询数据
    const offset = (page - 1) * page_size;
    const sql = `
      SELECT 
        a.*,
        (SELECT GROUP_CONCAT(CONCAT(p2.person_id, ':', p2.person_name) SEPARATOR ',') 
         FROM ap_approval_flows f 
         LEFT JOIN tb_person p2 ON f.approver_id = p2.person_id 
         WHERE f.application_id = a.application_id 
         ORDER BY f.level) as approvers,
        (SELECT GROUP_CONCAT(type SEPARATOR ',') 
         FROM ap_output_attachments o 
         WHERE o.application_id = a.application_id) as output_types,
        (SELECT COUNT(*) 
         FROM ap_output_attachments o 
         WHERE o.application_id = a.application_id AND o.status = 'completed') as completed_output_count,
        (SELECT COUNT(*) 
         FROM ap_output_attachments o 
         WHERE o.application_id = a.application_id) as total_output_count
      FROM 
        ap_applications a
      WHERE 
        ${conditions.join(' AND ')}
      ORDER BY 
        a.created_at DESC
      LIMIT ? OFFSET ?
    `;

    const result = await this.app.mysql.query(sql, [...queryParams, page_size, offset]);

    return {
      list: result,
      pagination: {
        total,
        page: parseInt(page),
        page_size: parseInt(page_size),
        total_pages: Math.ceil(total / page_size)
      }
    };
  }

  /**
   * 更新产出记录
   * @param {Object} data - 产出记录数据
   * @param {number} data.attachment_id - 产出记录ID
   * @param {number} data.application_id - 审批申请ID
   * @param {number} data.uploaded_by - 上传人ID
   * @param {string} [data.file_url] - 附件URL
   * @param {string} [data.file_name] - 文件名
   * @param {number} [data.file_size] - 文件大小(字节)
   * @param {string} [data.description] - 产出说明
   * @param {string} [data.status] - 状态
   * @returns {Promise<Object>} 更新结果
   */
  async updateOutput(data) {
    const {
      app
    } = this;
    const conn = await app.mysql.beginTransaction(); // 开启事务

    try {
      // 1. 验证权限
      const outputResult = await conn.query(
        'SELECT o.*, a.applicant_id FROM ap_output_attachments o LEFT JOIN ap_applications a ON o.application_id = a.application_id WHERE o.attachment_id = ? AND o.application_id = ?',
        [data.attachment_id, data.application_id]
      );

      if (!outputResult.length) {
        await conn.rollback();
        return {
          error: 'not_found',
          message: '未找到该产出记录'
        };
      }

      const output = outputResult[0];

      // 2. 构建更新数据
      const updateData = {
        updated_at: new Date()
      };

      if (data.uploaded_by !== undefined) {
        updateData.uploaded_by = data.uploaded_by;
      }

      if (data.file_url !== undefined) {
        updateData.file_url = data.file_url;
      }

      if (data.file_name !== undefined) {
        updateData.file_name = data.file_name;
      }

      if (data.file_size !== undefined) {
        updateData.file_size = data.file_size;
      }

      if (data.description !== undefined) {
        updateData.description = data.description;
      }

      if (data.status !== undefined) {
        updateData.status = data.status;
      }

      // 如果上传了文件URL，自动更新状态为已完成
      if (data.file_url && !data.status) {
        updateData.status = 'completed';
        updateData.uploaded_at = new Date();
      }

      // 3. 执行更新
      await conn.update('ap_output_attachments', updateData, {
        where: {
          attachment_id: data.attachment_id,
          application_id: data.application_id
        }
      });

      await conn.commit(); // 提交事务

      // 4. 返回更新后的产出记录
      return await this.findOutputById(data.attachment_id);

    } catch (error) {
      await conn.rollback(); // 回滚事务
      throw error;
    }
  }

  /**
   * 根据ID获取产出记录
   * @param {number} id - 产出记录ID
   * @returns {Promise<Object|null>} 产出记录，不存在时返回null
   */
  async findOutputById(id) {
    const result = await this.app.mysql.query(
      'SELECT * FROM ap_output_attachments WHERE attachment_id = ?',
      [id]
    );

    return result[0] || null;
  }

  /**
   * 根据审批申请ID获取产出记录列表
   * @param {number} applicationId - 审批申请ID
   * @returns {Promise<Array>} 产出记录列表
   */
  async findOutputsByApplicationId(applicationId) {
    const result = await this.app.mysql.query(
      'SELECT * FROM ap_output_attachments WHERE application_id = ? ORDER BY type',
      [applicationId]
    );

    return result;
  }

  /**
   * 获取与指定人员相关的审批申请（作为申请人或工作成员）
   * @param {Object} params - 查询参数
   * @param {number} params.person_id - 人员ID
   * @param {number} [params.page=1] - 页码
   * @param {number} [params.page_size=10] - 每页条数
   * @returns {Promise<Object>} 审批申请列表和分页信息
   */
  async getApplicationsByPerson(params) {
    const {
      person_id,
      page = 1,
      page_size = 10
    } = params;

    const offset = (page - 1) * page_size;

    // 构建查询条件 - 修复：移除is_deleted条件
    const sql = `
    SELECT 
      a.*,
      p.person_name as applicant_name,
      GROUP_CONCAT(DISTINCT CONCAT(
        '{"attachment_id":', o.attachment_id,
        ',"type":"', o.type, '"',
        ',"description":"', REPLACE(o.description, '"', '\\"'), '"',
        ',"file_url":"', o.file_url, '"',
        ',"file_name":"', o.file_name, '"',
        ',"file_size":', o.file_size,
        ',"status":"', o.status, '"',
        ',"uploaded_by":', o.uploaded_by,
        ',"uploaded_at":"', o.uploaded_at, '"'
        '}'
      ) SEPARATOR ',') as attachments
    FROM 
      ap_applications a
    LEFT JOIN 
      tb_person p ON a.applicant_id = p.person_id
    LEFT JOIN 
      ap_output_attachments o ON a.application_id = o.application_id
    WHERE 
      (
        a.applicant_id = ?
        OR FIND_IN_SET(?, a.work_members)
      )
    GROUP BY 
      a.application_id
    ORDER BY 
      a.created_at DESC
    LIMIT ? OFFSET ?
  `;

    // 计算总数 - 修复：移除is_deleted条件
    const countSql = `
    SELECT COUNT(*) as total 
    FROM ap_applications a
    WHERE 
      (
        a.applicant_id = ?
        OR FIND_IN_SET(?, a.work_members)
      )
  `;

    const totalResult = await this.app.mysql.query(countSql, [person_id, person_id]);
    const total = totalResult[0].total;

    // 查询数据
    const result = await this.app.mysql.query(sql, [person_id, person_id, page_size, offset]);

    // 处理附件数据（将JSON字符串转换为对象数组）
    const processedResult = result.map(item => {
      let attachments = [];
      if (item.attachments) {
        try {
          attachments = JSON.parse(`[${item.attachments}]`);
        } catch (error) {
          this.ctx.logger.error(`解析附件数据失败，application_id: ${item.application_id}`, error);
          attachments = [];
        }
      }

      return {
        ...item,
        attachments
      };
    });

    return {
      list: processedResult,
      pagination: {
        total,
        page: parseInt(page),
        page_size: parseInt(page_size),
        total_pages: Math.ceil(total / page_size)
      }
    };
  }
}

module.exports = ApprovalService;